<?php
/**
 * This file is part of the Agora distribution. 
 * Detailed copyright and licensing information can be found
 * in the gpl-3.0.txt file which should be included in the distribution.
 * 
 * @version		$Id: 3.0.14 Olympus 2010-09-28 rammstein4o $
 * @copyright  2007 - 2010 jVitals
 * @license   GPLv3 Open Source
 * @link       http://jvitals.com
 * @since      File available since initial release
 */
	defined ('IN_AGORA') or die;

	class ForumModel extends Model
	{
		var $id;
		function __construct()
		{
			parent::__construct('forums');
		}

		function loadSubforumsCounters($forum_id)
		{
			$this->db->bind('forum_id',$forum_id,'integer');
			$this->db->setQuery('SELECT id, num_topics, num_posts FROM ##__forums WHERE parent_forum_id = :forum_id');
			$subforums = $this->db->loadAssocList();
			$num_topics = 0;
			$num_posts = 0;
			
			foreach ($subforums as $subforum) {
				list($sub_topics,$sub_posts) = $this->loadSubforumsCounters($subforum['id']);
				$num_topics += $subforum['num_topics'] + $sub_topics;
				$num_posts += $subforum['num_posts'] + $sub_posts;

			}
			return array($num_topics,$num_posts);
		}

		function orderUp($forum_id)
		{
			$this->db->bind('id',$forum_id,'integer');
			$this->db->setQuery('SELECT disp_position, parent_forum_id, cat_id FROM ##__forums WHERE id=:id');
			list($old_pos, $parent_id, $cat_id) = $this->db->loadRow();

			$this->db->bind('order',$old_pos,'integer');
			$this->db->bind('parent_id',$parent_id,'integer');
			$this->db->bind('cat_id',$cat_id,'integer');
			$this->db->setQuery('SELECT id FROM ##__forums'.
							' WHERE disp_position < :order'.
							' AND cat_id = :cat_id'.
							' AND parent_forum_id = :parent_id'.
							' ORDER BY disp_position DESC LIMIT 1');
			$next_forum = $this->db->loadResult();
			if (!is_null($next_forum)) {
				$this->db->bind('next_forum',$next_forum,'integer');
				$this->db->setQuery('UPDATE ##__forums SET disp_position = disp_position + 1 WHERE  id = :next_forum');
				$this->db->query();
			}

			$this->db->bind('id',$forum_id,'integer');
			$this->db->setQuery('UPDATE ##__forums SET disp_position = disp_position - 1 WHERE id=:id');
			$this->db->query();
			$this->reorder();
		}

		function orderDown($forum_id)
		{
			$this->db->bind('id',$forum_id,'integer');
			$this->db->setQuery('SELECT disp_position, parent_forum_id, cat_id FROM ##__forums WHERE id=:id');
			list($old_pos, $parent_id, $cat_id) = $this->db->loadRow();

			$this->db->bind('order',$old_pos,'integer');
			$this->db->bind('parent_id',$parent_id,'integer');
			$this->db->bind('cat_id',$cat_id,'integer');
			$this->db->setQuery('SELECT id FROM ##__forums'.
							' WHERE disp_position > :order'.
							' AND cat_id = :cat_id'.
							' AND parent_forum_id = :parent_id'.
							' ORDER BY disp_position ASC LIMIT 1');
			$next_forum = $this->db->loadResult();
			if (!is_null($next_forum)) {
				$this->db->bind('next_forum',$next_forum,'integer');
				$this->db->setQuery('UPDATE ##__forums SET disp_position = disp_position - 1 WHERE  id = :next_forum');
				$this->db->query();
			}

			$this->db->bind('id',$forum_id,'integer');
			$this->db->setQuery('UPDATE ##__forums SET disp_position = disp_position + 1 WHERE id=:id');
			$this->db->query();
			$this->reorder();
		}

		function reorder()
		{
			$this->db->setQuery('SELECT id, disp_position, parent_forum_id, cat_id FROM ##__forums ORDER BY cat_id, parent_forum_id, disp_position, id');
			$forums = $this->db->loadAssocList();
			$n = 1;
			$parent = -1;
			$cat_id = -1;
			foreach ($forums as $forum) {
				if ($cat_id != $forum['cat_id']) {
					$n = 1;
					$cat_id = $forum['cat_id'];
				}

				if ($parent != $forum['parent_forum_id']) {
					$parent = $forum['parent_forum_id'];
					$n = 1;
				}

				if ($forum['disp_position'] != $n) {
					$this->db->bind('order',$n,'integer');
					$this->db->bind('id',$forum['id'],'integer');
					$this->db->setQuery('UPDATE ##__forums SET disp_position = :order WHERE id=:id');
					$this->db->query();
				}
				$n += 1;
			}
		}


		function setOrder($forum_id, $disp_position)
		{
			$this->db->bind('id',$forum_id,'integer');
			$this->db->bind('order',$disp_position,'integer');
			$this->db->setQuery('UPDATE ##__forums SET disp_position=:order WHERE id=:id');
			$this->db->query();
		}
		
		function expireForum($forum_id, $closed = 1)
		{
			$this->db->bind('id',$forum_id,'integer');
			$this->db->bind('closed',$closed,'integer');
			$this->db->bind('enable_sched',0,'integer');
			$this->db->bind('sched_end',NULL, NULL);
			$this->db->setQuery('UPDATE ##__forums SET closed=:closed, enable_sched=:enable_sched, sched_end=:sched_end  WHERE id=:id');
			return $this->db->query();
		}
		
		function hasTopic($forum_id, $user_name )
		{
			$this->db->bind('forum_id',$forum_id,'integer');
			$this->db->bind('poster',$user_name,'string');
			
			$this->db->setQuery('SELECT COUNT(*) FROM ##__topics WHERE poster=:poster AND forum_id=:forum_id');
			return $this->db->loadResult();
		}
		
		function load($id)
		{
			$this->db->bind('forum_id',$id,'integer');
			$this->db->setQuery('SELECT UNIX_TIMESTAMP(forum.sched_end) AS expires, forum.*, cat.id AS cat_id, cat.cat_name'.
					' FROM ##__forums AS forum '.
					' INNER JOIN ##__categories AS cat'.
					'  ON forum.cat_id = cat.id'.
					' WHERE forum.id = :forum_id');
				
			return $this->db->loadAssoc();
		}

		function loadSubjectsMess($forum_ids)
		{
			if (!is_array($forum_ids) || empty($forum_ids)) return array();
			$this->db->bindList('ids',$forum_ids,'integer');
			$this->db->setQuery('SELECT forum.id as forum_id, topic.id as topic_id, topic.num_replies, forum.cat_id, topic.subject, post.message, topic.sticky, topic.closed'.
						' FROM ##__topics AS topic'.
						' INNER JOIN ##__posts AS post'.
						'  ON post.topic_id = topic.id'.
						' INNER JOIN ##__forums AS forum'.
						'  ON forum.last_post_id = post.id'.
						' WHERE forum.id IN (:ids)');
			return $this->db->loadAssocList();
		}


		function loadTopics($id, $per_page = null, $page = null, $sort_field = '', $order_dir='DESC') {
			$limit = $this->getLimit($per_page, $page); //TODO JOOPPORT проконтролировать, где вызывается эта функция //и что дальше??

			/*
			$page = intval($page) - 1;
			$per_page = intval($per_page);
			if ($per_page < 1) {
				$per_page = 5;
			}
			$start = $page * $per_page;*/

			if (!empty($sort_field)) {
				$sort_field = ", $sort_field $order_dir";
			}
			
			$this->db->bind('id', $id, 'integer');
			
			$this->db->setQuery('SELECT t.*, j_u.name as u_fullname, j_u2.name as lu_fullname, p.message as postcontent ' . 
								'FROM ##__topics as t ' .
								'LEFT JOIN #__users AS j_u ' .
									'ON t.poster = j_u.username ' .
								'LEFT JOIN #__users AS j_u2 ' .
									'ON t.last_poster = j_u2.username ' .
								'LEFT JOIN ##__posts AS p ' .
									'ON t.last_post_id = p.id AND t.last_post_id <> 0 ' .
								'WHERE t.forum_id = :id AND t.approved = 1 ' .
								'ORDER BY t.sticky DESC ' . $sort_field . $limit);

			return $this->db->loadAssocList();
		}

		function add($form)
		{
			$cat_id = intval($form['cat_id']);
			$parent_id = intval($form['parent_forum_id']);

			$this->setQuery("SELECT COALESCE(MAX(disp_position)+1,1) FROM ##__forums WHERE cat_id = $cat_id AND parent_forum_id = $parent_id");
			$disp = $this->db->loadResult();

			$forum_name = $this->db->Quote($form['forum_name']);
			$forum_desc = $this->db->Quote($form['forum_desc']);
			$forum_mdesc = $this->db->Quote($form['forum_mdesc']);
			$forum_key = $this->db->Quote($form['forum_key']);

			$this->setQuery("INSERT INTO ##__forums".
				" (cat_id, forum_name, forum_desc, forum_mdesc, forum_key, disp_position, parent_forum_id) VALUES".
				" ($cat_id, $forum_name ,$forum_desc, $forum_mdesc, $forum_key ,$disp, $parent_id)"
				);

    		$this->db->query();

			return $this->db->insertid();
		}

		function getCategory($forum_id)
		{
			$this->setQuery('SELECT cat_id FROM ##__forums WHERE id = ' . intval($forum_id));
			return $this->db->loadResult();
		}

		/// looks like died code
/*		function addSubforum($forum_name, $parent_id)
		{
			$this->db->bind('parent_id',$parent_id,'integer');
			$this->setQuery('SELECT COALESCE(MAX(disp_position)+1,1) FROM ##__forums WHERE parent_forum_id = :parent_id');
			$disp = $this->db->loadResult();

			$this->db->bind('forum_name',$forum_name,'string');
			$this->db->bind('parent_id',$parent_id,'integer');
			$this->db->setQuery('INSERT INTO ##__forums'.
				' (forum_name, cat_id, disp_position, parent_forum_id)'.
				' VALUES (:forum_name, '.$cat_id.', $disp, $q_parent_id)');

    		$this->db->query();
			return $this->db->insertid();
		}*/

		function loadSubforums($parent_id)
		{
			$this->db->bind('parent_id',$parent_id,'integer');
			$this->db->setQuery('SELECT * FROM ##__forums WHERE parent_forum_id = :parent_id ORDER BY disp_position ASC');
			$subforums = $this->db->loadAssocList();
			foreach ($subforums as $id=>$sub) {
				$this->db->bind('sub_id',$sub['id'],'integer');
				$this->db->setQuery('SELECT COUNT(*) FROM ##__forums WHERE parent_forum_id = :sub_id');
				$subforums[$id]['subforum_count'] = $this->db->loadResult();
			}
			return $subforums;
		}

		function loadNames()
		{
			$sql = "SELECT id,forum_name FROM {$this->table}";
			$this->db->setQuery($sql);
			$list = $this->db->loadRowList();
			if (!$list) return false;
			$data = array();
			foreach ($list as $result) {
				$fname = $result[0];
				$fvalue = $result[1];
				$data[$fname] = $fvalue;
			}
			return $data;
		}

		function setAnnounce($forum_id,$ann_text,$visible)
		{
			
			$q_forum_id = $this->db->Quote($forum_id);
			$q_ann = $this->db->Quote($ann_text);
			$q_visible = $visible ? 1 : 0;

			$this->db->bind('forum_id',$forum_id,'integer');
			$this->db->setQuery('SELECT COUNT(*)'.
				' FROM ##__annonces WHERE forum_id = :forum_id');

		    $exists = $this->db->loadResult() > 0 ? true : false;

		    if ($exists) {
		    	$this->db->setQuery('UPDATE ##__annonces SET'.
		    			" forum_annonce = $q_ann, forum_annonce_visible = $q_visible".
		    			" WHERE forum_id = $q_forum_id");
		    } else {
		    	$this->db->setQuery('INSERT INTO ##__annonces'.
		    			' (forum_id, forum_annonce, forum_annonce_visible)'.
		    			" VALUES ($q_forum_id, $q_ann, $q_visible)");
		    }
			$this->db->query();
		}

		function delete($forum_id)
		{
			$this->db->bind('id',$forum_id,'integer');
			$this->db->setQuery('SELECT id FROM ##__forums WHERE parent_forum_id=:id');
			$children = $this->db->loadResultArray();
			foreach ($children as $child) {
				$this->delete($child);
			}

			$this->db->bind('id',$forum_id,'integer');
			$this->db->setQuery('SELECT id FROM ##__topics WHERE forum_id=:id');
			$topics = $this->db->loadResultArray();

			$topic_model = & Model::getInstance('TopicModel');
			foreach ($topics as $topic) {
				$topic_model->delete($topic);
			}

			$this->db->bind('id',$forum_id,'integer', true);
			
			$this->db->setQuery('DELETE FROM ##__topics WHERE forum_id=:id');
			$this->db->query();

			$this->db->setQuery('DELETE FROM ##__forums WHERE id=:id');
			$this->db->query();

			$this->db->setQuery('DELETE FROM ##__group_forum WHERE forum_id=:id');
			$this->db->query();

			$this->db->setQuery('DELETE FROM ##__feeds WHERE forum_id=:id');
			$this->db->query();
			
			$this->db->clear();
		}
		
		function moveTopicsTo($forum_id, $to_forum_id)
		{
			$this->db->bind('id',$forum_id,'integer');
			$this->db->setQuery('SELECT id FROM ##__forums WHERE parent_forum_id=:id');
			$children = $this->db->loadResultArray();
			foreach ($children as $child) {
				$this->moveTopicsTo($child, $to_forum_id);
			}

			$this->db->bind('id',$forum_id,'integer');
			$this->db->setQuery('SELECT id, num_replies FROM ##__topics WHERE forum_id=:id');
			$topics = $this->db->loadAssocList();

			foreach ($topics as $topic) {
			    $this->db->bind('toid',$to_forum_id,'integer');
				$this->db->setQuery('UPDATE ##__topics SET forum_id = :toid WHERE id =' .$topic['id']);
				$this->db->query();
				$this->db->setQuery('UPDATE ##__forums SET num_topics = num_topics+1, num_posts = num_posts+' .$topic['num_replies']. ' WHERE id ='.$to_forum_id);
				$this->db->query();
				
			}

			$this->db->bind('id',$forum_id,'integer', true);
			
			$this->db->setQuery('DELETE FROM ##__forums WHERE id=:id');
			$this->db->query();

			$this->db->setQuery('DELETE FROM ##__group_forum WHERE forum_id=:id');
			$this->db->query();

			$this->db->setQuery('DELETE FROM ##__feeds WHERE forum_id=:id');
			$this->db->query();
			
			$this->db->clear();
		}

		function deleteAnnounce($forum_id)
		{
			$this->db->bind('forum_id',$forum_id,'integer');
			$this->setQuery('DELETE FROM ##__annonces WHERE forum_id = :forum_id');
			$this->db->query();
		}
		
		function moveUp($id)
		{
			$this->db->bind('forum_id',$forum_id,'integer',true);
			$this->setQuery('SELECT cat_id, parent_forum_id, disp_position FROM ##__forums WHERE id = :forum_id');
			
			list($cat_id,$parent_id,$old_disp) = $this->db->loadRow();
			
			$this->setQuery("SELECT MIN(disp_position) FROM ##__forums WHERE cat_id = $cat_id AND parent_forum_id = $parent_id");
			$min = $this->db->loadResult();
			if ($old_disp <= $min) return;
			
			$this->setQuery('UPDATE ##__forums'.
					' SET disp_position = disp_position + 1'.
					" WHERE cat_id = $cat_id AND parent_forum_id = $parent_id".
					" AND disp_position < $old_disp".
					' ORDER BY disp_position DESC'.
					' LIMIT 1');
			$this->db->query();
			
			$this->setQuery('UPDATE ##__forums'.
					' SET disp_position = disp_position - 1'.
					" WHERE id = :forum_id");
			$this->db->query();

			$this->db->clear();
		}
		
		function moveDown($id)
		{
			$this->db->bind('forum_id',$forum_id,'integer',true);
			$this->setQuery('SELECT cat_id, parent_forum_id, disp_position FROM ##__forums WHERE id = :forum_id');
			list($cat_id,$parent_id,$old_disp) = $this->db->loadRow();
			
			$this->setQuery("SELECT MAX(disp_position) FROM ##__forums WHERE cat_id = $cat_id AND parent_forum_id = $parent_id");
			$max = $this->db->loadResult();
			if ($old_disp >= $max) return;
			
			$this->setQuery("UPDATE ##__forums".
					" SET disp_position = disp_position - 1".
					" WHERE cat_id = $cat_id AND parent_forum_id = $parent_id".
					" AND disp_position > $old_disp".
					" ORDER BY disp_position ASC".
					" LIMIT 1");
			$this->db->query();
			
			$this->setQuery("UPDATE ##__forums".
					" SET disp_position = disp_position + 1".
					" WHERE id = :forum_id");
			$this->db->query();
			$this->db->clear();
		}
		
		function copy($id)
		{
			$id = intval($id);
			$this->setQuery("SELECT * FROM ##__forums WHERE id='$id' LIMIT 1");
			$forum = $this->db->loadAssoc();
			unset($forum['id']);
			$forum['forum_name'] = 'Copy of '.$forum['forum_name'];
			$forum['disp_position'] = intval($forum['disp_position']) + 1;
			$this->setQuery("UPDATE ##__forums SET disp_position=disp_position+1 WHERE 
					parent_forum_id = '" . $forum['parent_forum_id'] . "' 
					AND cat_id = '".$forum['cat_id']."'
					AND disp_position >= '" . $forum['disp_position']."'");
			$this->db->query();
			$this->setQuery("INSERT INTO ##__forums (`".implode("`,`",array_keys($forum))."`) VALUES ('".implode("','",$forum)."')");
			$this->db->query();
		}
		
		function disable($id)
		{
			$id = intval($id);
			$this->setQuery("UPDATE ##__forums SET enable = 0 WHERE id = '$id'");
			$this->db->query();
		}
		
		function enable($id)
		{
			$id = intval($id);
			$this->setQuery("UPDATE ##__forums SET enable = 1 WHERE id = '$id'");
			$this->db->query();
		}
		
		function isSubscribed($forum_id, $user_id)
		{
			$this->db->bind('forum_id',$forum_id,'integer');
			$this->db->bind('user_id',$user_id,'integer');
			
			$this->db->setQuery('SELECT COUNT(*) FROM ##__subscriptions WHERE user_id=:user_id AND forum_id=:forum_id');
			return $this->db->loadResult();

		}

		function updateChildrenCategory($forum_id, $cat_id)
		{
			$this->db->bind('parent_id',$forum_id,'integer');
			$this->db->setQuery('SELECT id FROM ##__forums WHERE parent_forum_id=:parent_id');
			$children = $this->db->loadResultArray();

			if (count($children) <= 0) return;

			$this->db->bind('cat_id',$cat_id,'integer');
			$this->db->bindList('ids',$children,'integer');
			$this->db->setQuery('UPDATE ##__forums SET cat_id=:cat_id WHERE id IN (:ids)');
			$this->db->query();
			
			foreach ($children as $id) {
				$this->updateChildrenCategory($id, $cat_id);
			}
		}
		
		
		
		//JOOPPORT FUNCTIONS
		
		function joop_disable($id)
		{
			$id = intval($id);
			$this->setQuery("UPDATE ##__forums SET forum_type = 0 WHERE id = '$id'");
			$this->db->query();
		}
		
		function joop_enable($id)
		{
			$id = intval($id);
			$this->setQuery("UPDATE ##__forums SET forum_type = 1 WHERE id = '$id'");
			$this->db->query();
		}
		
		function joop_getQuest($id)
		{
			$id = intval($id);
			$this->setQuery("SELECT quest_group FROM ##__forums WHERE id = '$id'");
			return $this->db->loadResult();
		}
		
	}
?>
